{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# More JOIN operations\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/1/10/Movie-er.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 1962 movies\n",
    "\n",
    "List the films where the **yr** is 1962 [Show **id, title**]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "86 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10212</td>\n",
       "        <td>A Kind of Loving</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10329</td>\n",
       "        <td>A Symposium on Popular Songs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10347</td>\n",
       "        <td>A Very Private Affair (Vie PrivÃ©e)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10648</td>\n",
       "        <td>An Autumn Afternoon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10868</td>\n",
       "        <td>Atraco a las tres</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11006</td>\n",
       "        <td>Barabbas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11053</td>\n",
       "        <td>Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11199</td>\n",
       "        <td>Big and Little Wong Tin Bar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11230</td>\n",
       "        <td>Billy Budd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11234</td>\n",
       "        <td>Billy Rose&#x27;s Jumbo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11242</td>\n",
       "        <td>Birdman of Alcatraz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11373</td>\n",
       "        <td>Boccaccio &#x27;70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11391</td>\n",
       "        <td>Bon Voyage!</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11439</td>\n",
       "        <td>Boys&#x27; Night Out</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11692</td>\n",
       "        <td>Cape Fear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11735</td>\n",
       "        <td>Carnival of Souls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11753</td>\n",
       "        <td>Carry On Cruising</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12368</td>\n",
       "        <td>David and Lisa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12384</td>\n",
       "        <td>Days of Wine and Roses</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12710</td>\n",
       "        <td>Dr. No</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">86 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[(10212, 'A Kind of Loving'),\n",
       " (10329, 'A Symposium on Popular Songs'),\n",
       " (10347, 'A Very Private Affair (Vie PrivÃ©e)'),\n",
       " (10648, 'An Autumn Afternoon'),\n",
       " (10868, 'Atraco a las tres'),\n",
       " (11006, 'Barabbas'),\n",
       " (11053, 'Battle Beyond the Sun (Ð\\x9dÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)'),\n",
       " (11199, 'Big and Little Wong Tin Bar'),\n",
       " (11230, 'Billy Budd'),\n",
       " (11234, \"Billy Rose's Jumbo\"),\n",
       " (11242, 'Birdman of Alcatraz'),\n",
       " (11373, \"Boccaccio '70\"),\n",
       " (11391, 'Bon Voyage!'),\n",
       " (11439, \"Boys' Night Out\"),\n",
       " (11692, 'Cape Fear'),\n",
       " (11735, 'Carnival of Souls'),\n",
       " (11753, 'Carry On Cruising'),\n",
       " (12368, 'David and Lisa'),\n",
       " (12384, 'Days of Wine and Roses'),\n",
       " (12710, 'Dr. No'),\n",
       " (12817, \"L'Eclisse\"),\n",
       " (12967, 'Tutti a casa'),\n",
       " (12992, 'Experiment in Terror'),\n",
       " (13010, 'Eyes Without a Face'),\n",
       " (13484, 'Gay Purr-ee'),\n",
       " (13534, 'Gigot'),\n",
       " (13641, 'Gorath'),\n",
       " (13727, 'Gypsy'),\n",
       " (13741, 'Half Ticket'),\n",
       " (13798, 'Harakiri'),\n",
       " (14317, 'In Search of the Castaways'),\n",
       " (14454, \"It's Only Money\"),\n",
       " (14550, 'Jigsaw'),\n",
       " (14718, 'Kid Galahad'),\n",
       " (14860, 'La commare secca'),\n",
       " (14873, 'La notte'),\n",
       " (14972, 'Lawrence of Arabia'),\n",
       " (15088, 'Life for Ruth'),\n",
       " (15173, 'Lolita'),\n",
       " (15182, \"Long Day's Journey into Night\"),\n",
       " (15247, 'Love at Twenty'),\n",
       " (15297, 'Lycanthropus'),\n",
       " (15397, 'Mamma Roma'),\n",
       " (15564, \"Merrill's Marauders\"),\n",
       " (15752, 'Mother Joan of the Angels'),\n",
       " (15779, 'Mr. Hobbs Takes a Vacation'),\n",
       " (15840, 'Mutiny on the Bounty'),\n",
       " (16203, 'On the Beat'),\n",
       " (16295, 'Os Cafajestes'),\n",
       " (16367, 'Panic in Year Zero!'),\n",
       " (16462, 'Period of Adjustment'),\n",
       " (16485, 'Phaedra'),\n",
       " (16533, 'Pitfall'),\n",
       " (16622, 'Pressure Point'),\n",
       " (16661, 'Prison'),\n",
       " (16675, 'Professor'),\n",
       " (16945, 'Ride the High Country'),\n",
       " (17148, 'Salvatore Giuliano'),\n",
       " (17296, 'Sergeants 3'),\n",
       " (17804, 'State Fair'),\n",
       " (17944, 'Sundays and Cybele'),\n",
       " (18001, 'Sweet Bird of Youth (film)'),\n",
       " (18127, 'Term of Trial'),\n",
       " (18171, 'That Touch of Mink'),\n",
       " (18177, 'The 300 Spartans'),\n",
       " (18730, 'The Counterfeit Traitor'),\n",
       " (18941, 'The Exterminating Angel'),\n",
       " (19034, 'The Four Days of Naples'),\n",
       " (19430, 'The Intruder'),\n",
       " (19520, 'The L-Shaped Room'),\n",
       " (19679, 'The Loneliness of the Long Distance Runner'),\n",
       " (19694, 'The Longest Day'),\n",
       " (19795, 'The Man Who Shot Liberty Valance'),\n",
       " (19808, 'The Manchurian Candidate'),\n",
       " (19933, 'The Music Man'),\n",
       " (20114, 'The Phantom of the Opera'),\n",
       " (20161, 'The Premature Burial'),\n",
       " (20730, 'The Trial'),\n",
       " (21051, 'Through a Glass Darkly'),\n",
       " (21110, 'To Kill a Mockingbird'),\n",
       " (21189, 'Tower of London'),\n",
       " (21324, 'Two Half Times in Hell'),\n",
       " (21462, 'Varan the Unbelievable'),\n",
       " (21494, 'Village of Daughters'),\n",
       " (21673, 'What Ever Happened to Baby Jane?'),\n",
       " (21761, \"Who's Got the Action?\")]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT id, title\n",
    "  FROM movie\n",
    "    WHERE yr=1962;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. When was Citizen Kane released?\n",
    "\n",
    "Give year of 'Citizen Kane'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1941</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1941,)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT yr FROM movie\n",
    "WHERE title = 'Citizen Kane';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Star Trek movies\n",
    "\n",
    "List all of the Star Trek movies, include the **id**, **title** and **yr** (all of these movies include the words Star Trek in the title). Order results by year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "11 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>title</th>\n",
       "        <th>yr</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17772</td>\n",
       "        <td>Star Trek: The Motion Picture</td>\n",
       "        <td>1979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17775</td>\n",
       "        <td>Star Trek II: The Wrath of Khan</td>\n",
       "        <td>1982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17776</td>\n",
       "        <td>Star Trek III: The Search for Spock</td>\n",
       "        <td>1984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17777</td>\n",
       "        <td>Star Trek IV: The Voyage Home</td>\n",
       "        <td>1986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17779</td>\n",
       "        <td>Star Trek V: The Final Frontier</td>\n",
       "        <td>1989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17780</td>\n",
       "        <td>Star Trek VI: The Undiscovered Country</td>\n",
       "        <td>1991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17774</td>\n",
       "        <td>Star Trek Generations</td>\n",
       "        <td>1994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17770</td>\n",
       "        <td>Star Trek: First Contact</td>\n",
       "        <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17771</td>\n",
       "        <td>Star Trek: Insurrection</td>\n",
       "        <td>1998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17778</td>\n",
       "        <td>Star Trek Nemesis</td>\n",
       "        <td>2002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>17773</td>\n",
       "        <td>Star Trek</td>\n",
       "        <td>2009</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(17772, 'Star Trek: The Motion Picture', 1979),\n",
       " (17775, 'Star Trek II: The Wrath of Khan', 1982),\n",
       " (17776, 'Star Trek III: The Search for Spock', 1984),\n",
       " (17777, 'Star Trek IV: The Voyage Home', 1986),\n",
       " (17779, 'Star Trek V: The Final Frontier', 1989),\n",
       " (17780, 'Star Trek VI: The Undiscovered Country', 1991),\n",
       " (17774, 'Star Trek Generations', 1994),\n",
       " (17770, 'Star Trek: First Contact', 1996),\n",
       " (17771, 'Star Trek: Insurrection', 1998),\n",
       " (17778, 'Star Trek Nemesis', 2002),\n",
       " (17773, 'Star Trek', 2009)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT id, title, yr\n",
    "  FROM movie\n",
    "    WHERE title LIKE '%Star Trek%'\n",
    "    ORDER BY yr;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. id for actor Glenn Close\n",
    "\n",
    "What **id** number does the actor 'Glenn Close' have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>140</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(140,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT actor.id\n",
    "  FROM actor\n",
    "    WHERE name = 'Glenn Close';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. id for Casablanca\n",
    "\n",
    "What is the **id** of the film 'Casablanca'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>11768</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(11768,)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT id FROM movie\n",
    "  WHERE title = 'Casablanca';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Cast list for Casablanca\n",
    "\n",
    "Obtain the cast list for 'Casablanca'.\n",
    "\n",
    "> _what is a cast list?_  \n",
    "> The cast list is the names of the actors who were in the movie.\n",
    "\n",
    "Use **movieid=11768**, (or whatever value you got from the previous question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "32 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peter Lorre</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Qualen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Madeleine LeBeau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jack Benny</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dan Seymour</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Norma Varden</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ingrid Bergman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Conrad Veidt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Leon Belasco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Humphrey Bogart</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sydney Greenstreet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Leonid Kinskey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Wolfgang Zilzer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Claude Rains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Curt Bois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Leo White</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ludwig StÃ¶ssel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Marcel Dalio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Paul Henreid</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Joy Page</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">32 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Peter Lorre',),\n",
       " ('John Qualen',),\n",
       " ('Madeleine LeBeau',),\n",
       " ('Jack Benny',),\n",
       " ('Dan Seymour',),\n",
       " ('Norma Varden',),\n",
       " ('Ingrid Bergman',),\n",
       " ('Conrad Veidt',),\n",
       " ('Leon Belasco',),\n",
       " ('Humphrey Bogart',),\n",
       " ('Sydney Greenstreet',),\n",
       " ('Leonid Kinskey',),\n",
       " ('Wolfgang Zilzer',),\n",
       " ('Claude Rains',),\n",
       " ('Curt Bois',),\n",
       " ('Leo White',),\n",
       " ('Ludwig StÃ¶ssel',),\n",
       " ('Marcel Dalio',),\n",
       " ('Paul Henreid',),\n",
       " ('Joy Page',),\n",
       " ('S. Z. Sakall',),\n",
       " ('Dooley Wilson',),\n",
       " ('William Edmunds',),\n",
       " ('Gregory Gaye',),\n",
       " ('Torben Meyer',),\n",
       " ('Georges Renavent',),\n",
       " ('Jean Del Val',),\n",
       " ('Louis V. Arco',),\n",
       " ('Trude Berliner',),\n",
       " ('Ilka GrÃ¼nig',),\n",
       " ('Richard Ryen',),\n",
       " ('Hans Twardowski',)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name \n",
    "  FROM casting INNER JOIN actor ON casting.actorid=actor.id\n",
    "    WHERE movieid=11768;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Alien cast list\n",
    "\n",
    "Obtain the cast list for the film 'Alien'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "7 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Hurt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sigourney Weaver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Yaphet Kotto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harry Dean Stanton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ian Holm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tom Skerritt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Veronica Cartwright</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('John Hurt',),\n",
       " ('Sigourney Weaver',),\n",
       " ('Yaphet Kotto',),\n",
       " ('Harry Dean Stanton',),\n",
       " ('Ian Holm',),\n",
       " ('Tom Skerritt',),\n",
       " ('Veronica Cartwright',)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name\n",
    "  FROM (movie RIGHT JOIN casting ON movie.id=casting.movieid) INNER JOIN \n",
    "    actor ON actorid=actor.id\n",
    "    WHERE title='Alien';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Harrison Ford movies\n",
    "\n",
    "List the films in which 'Harrison Ford' has appeared"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "42 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A Hundred and One Nights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Air Force One</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>American Graffiti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Apocalypse Now</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Clear and Present Danger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cowboys &amp; Aliens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Crossing Over</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dead Heat on a Merry-Go-Round</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Extraordinary Measures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Firewall</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Force 10 From Navarone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hanover Street</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hawthorne of the U.S.A.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hollywood Homicide</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indiana Jones and the Kingdom of the Crystal Skull</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indiana Jones and the Last Crusade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indiana Jones and the Temple of Doom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jimmy Hollywood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>K-19: The Widowmaker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>More American Graffiti</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">42 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('A Hundred and One Nights',),\n",
       " ('Air Force One',),\n",
       " ('American Graffiti',),\n",
       " ('Apocalypse Now',),\n",
       " ('Clear and Present Danger',),\n",
       " ('Cowboys & Aliens',),\n",
       " ('Crossing Over',),\n",
       " ('Dead Heat on a Merry-Go-Round',),\n",
       " ('Extraordinary Measures',),\n",
       " ('Firewall',),\n",
       " ('Force 10 From Navarone',),\n",
       " ('Hanover Street',),\n",
       " ('Hawthorne of the U.S.A.',),\n",
       " ('Hollywood Homicide',),\n",
       " ('Indiana Jones and the Kingdom of the Crystal Skull',),\n",
       " ('Indiana Jones and the Last Crusade',),\n",
       " ('Indiana Jones and the Temple of Doom',),\n",
       " ('Jimmy Hollywood',),\n",
       " ('K-19: The Widowmaker',),\n",
       " ('More American Graffiti',),\n",
       " ('Morning Glory',),\n",
       " ('Patriot Games',),\n",
       " ('Presumed Innocent',),\n",
       " ('Raiders of the Lost Ark',),\n",
       " ('Random Hearts',),\n",
       " ('Regarding Henry',),\n",
       " ('Sabrina',),\n",
       " ('Sally of the Sawdust',),\n",
       " ('Shadows',),\n",
       " ('Six Days Seven Nights',),\n",
       " (\"Smilin' Through\",),\n",
       " ('Star Wars Episode IV: A New Hope',),\n",
       " ('Star Wars Episode V: The Empire Strikes Back',),\n",
       " ('Star Wars Episode VI: Return of the Jedi',),\n",
       " ('The Conversation',),\n",
       " (\"The Devil's Own\",),\n",
       " ('The Fugitive',),\n",
       " ('The Mosquito Coast',),\n",
       " (None,),\n",
       " ('What Lies Beneath',),\n",
       " ('Witness',),\n",
       " ('Working Girl',)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title\n",
    "  FROM (movie RIGHT JOIN casting ON movie.id=movieid) JOIN actor ON actorid=actor.id\n",
    "    WHERE name = 'Harrison Ford';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Harrison Ford as a supporting actor\n",
    "\n",
    "List the films where 'Harrison Ford' has appeared - but not in the starring role. [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "19 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A Hundred and One Nights</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>American Graffiti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Apocalypse Now</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cowboys &amp; Aliens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dead Heat on a Merry-Go-Round</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Extraordinary Measures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Force 10 From Navarone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hawthorne of the U.S.A.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jimmy Hollywood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>More American Graffiti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Morning Glory</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sally of the Sawdust</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shadows</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Smilin&#x27; Through</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Star Wars Episode IV: A New Hope</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Star Wars Episode V: The Empire Strikes Back</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Star Wars Episode VI: Return of the Jedi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Conversation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Working Girl</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('A Hundred and One Nights',),\n",
       " ('American Graffiti',),\n",
       " ('Apocalypse Now',),\n",
       " ('Cowboys & Aliens',),\n",
       " ('Dead Heat on a Merry-Go-Round',),\n",
       " ('Extraordinary Measures',),\n",
       " ('Force 10 From Navarone',),\n",
       " ('Hawthorne of the U.S.A.',),\n",
       " ('Jimmy Hollywood',),\n",
       " ('More American Graffiti',),\n",
       " ('Morning Glory',),\n",
       " ('Sally of the Sawdust',),\n",
       " ('Shadows',),\n",
       " (\"Smilin' Through\",),\n",
       " ('Star Wars Episode IV: A New Hope',),\n",
       " ('Star Wars Episode V: The Empire Strikes Back',),\n",
       " ('Star Wars Episode VI: Return of the Jedi',),\n",
       " ('The Conversation',),\n",
       " ('Working Girl',)]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title\n",
    "  FROM (movie RIGHT JOIN casting ON movie.id=movieid) JOIN actor ON actorid=actor.id\n",
    "    WHERE name = 'Harrison Ford' AND ord > 1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Lead actors in 1962 movies\n",
    "\n",
    "List the films together with the leading star for all 1962 films."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "83 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>title</th>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A Kind of Loving</td>\n",
       "        <td>Alan Bates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A Symposium on Popular Songs</td>\n",
       "        <td>Paul Frees</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A Very Private Affair (Vie PrivÃ©e)</td>\n",
       "        <td>Brigitte Bardot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>An Autumn Afternoon</td>\n",
       "        <td>Chishu Ryu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Atraco a las tres</td>\n",
       "        <td>JosÃ© Luis LÃ³pez VÃ¡zquez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barabbas</td>\n",
       "        <td>Anthony Quinn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Battle Beyond the Sun (ÐÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)</td>\n",
       "        <td>Aleksandr Shvorin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Big and Little Wong Tin Bar</td>\n",
       "        <td>Jackie Chan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Billy Budd</td>\n",
       "        <td>Terence Stamp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Billy Rose&#x27;s Jumbo</td>\n",
       "        <td>Doris Day</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Birdman of Alcatraz</td>\n",
       "        <td>Burt Lancaster</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Boccaccio &#x27;70</td>\n",
       "        <td>Anita Ekberg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bon Voyage!</td>\n",
       "        <td>Fred MacMurray</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Boys&#x27; Night Out</td>\n",
       "        <td>Kim Novak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cape Fear</td>\n",
       "        <td>Gregory Peck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Carnival of Souls</td>\n",
       "        <td>Candace Hilligoss</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Carry On Cruising</td>\n",
       "        <td>Sid James</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>David and Lisa</td>\n",
       "        <td>Keir Dullea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Days of Wine and Roses</td>\n",
       "        <td>Jack Lemmon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Dr. No</td>\n",
       "        <td>Sean Connery</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">83 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('A Kind of Loving', 'Alan Bates'),\n",
       " ('A Symposium on Popular Songs', 'Paul Frees'),\n",
       " ('A Very Private Affair (Vie PrivÃ©e)', 'Brigitte Bardot'),\n",
       " ('An Autumn Afternoon', 'Chishu Ryu'),\n",
       " ('Atraco a las tres', 'JosÃ© Luis LÃ³pez VÃ¡zquez'),\n",
       " ('Barabbas', 'Anthony Quinn'),\n",
       " ('Battle Beyond the Sun (Ð\\x9dÐµÐ±Ð¾ Ð·Ð¾Ð²ÐµÑ‚)', 'Aleksandr Shvorin'),\n",
       " ('Big and Little Wong Tin Bar', 'Jackie Chan'),\n",
       " ('Billy Budd', 'Terence Stamp'),\n",
       " (\"Billy Rose's Jumbo\", 'Doris Day'),\n",
       " ('Birdman of Alcatraz', 'Burt Lancaster'),\n",
       " (\"Boccaccio '70\", 'Anita Ekberg'),\n",
       " ('Bon Voyage!', 'Fred MacMurray'),\n",
       " (\"Boys' Night Out\", 'Kim Novak'),\n",
       " ('Cape Fear', 'Gregory Peck'),\n",
       " ('Carnival of Souls', 'Candace Hilligoss'),\n",
       " ('Carry On Cruising', 'Sid James'),\n",
       " ('David and Lisa', 'Keir Dullea'),\n",
       " ('Days of Wine and Roses', 'Jack Lemmon'),\n",
       " ('Dr. No', 'Sean Connery'),\n",
       " (\"L'Eclisse\", 'Alain Delon'),\n",
       " ('Tutti a casa', 'Alberto Sordi'),\n",
       " ('Experiment in Terror', 'Glenn Ford'),\n",
       " ('Eyes Without a Face', 'Pierre Brasseur'),\n",
       " ('Gay Purr-ee', 'Judy Garland'),\n",
       " ('Gigot', 'Jackie Gleason'),\n",
       " ('Gorath', 'Ryo Ikebe'),\n",
       " ('Gypsy', 'Rosalind Russell'),\n",
       " ('Half Ticket', 'Kishore Kumar'),\n",
       " ('Harakiri', 'Tatsuya Nakadai'),\n",
       " ('In Search of the Castaways', 'Hayley Mills'),\n",
       " (\"It's Only Money\", 'Jerry Lewis'),\n",
       " ('Jigsaw', 'Jack Warner'),\n",
       " ('Kid Galahad', 'Elvis Presley'),\n",
       " ('La commare secca', 'Marisa Solinas'),\n",
       " ('La notte', 'Marcello Mastroianni'),\n",
       " ('Life for Ruth', 'Michael Craig'),\n",
       " (\"Long Day's Journey into Night\", 'Katharine Hepburn'),\n",
       " ('Love at Twenty', 'Jean-Pierre LÃ©aud'),\n",
       " ('Lycanthropus', 'Barbara Lass'),\n",
       " ('Mamma Roma', 'Anna Magnani'),\n",
       " ('Mother Joan of the Angels', 'Lucyna Winnicka'),\n",
       " ('Mr. Hobbs Takes a Vacation', 'James Stewart'),\n",
       " ('Mutiny on the Bounty', 'Marlon Brando'),\n",
       " ('On the Beat', 'Norman Wisdom'),\n",
       " ('Os Cafajestes', 'Daniel Filho'),\n",
       " ('Panic in Year Zero!', 'Ray Milland'),\n",
       " ('Period of Adjustment', 'Anthony Franciosa'),\n",
       " ('Phaedra', 'Melina Mercouri'),\n",
       " ('Pitfall', 'Hisashi Igawa'),\n",
       " ('Pressure Point', 'Sidney Poitier'),\n",
       " ('Prison', 'Doris Svedlund'),\n",
       " ('Professor', 'Shammi Kapoor'),\n",
       " ('Ride the High Country', 'Joel McCrea'),\n",
       " ('Salvatore Giuliano', 'Salvo Randone'),\n",
       " ('Sergeants 3', 'Frank Sinatra'),\n",
       " ('State Fair', 'Pat Boone'),\n",
       " ('Sundays and Cybele', 'Hardy KrÃ¼ger'),\n",
       " ('Sweet Bird of Youth (film)', 'Paul Newman'),\n",
       " ('Term of Trial', 'Laurence Olivier'),\n",
       " ('That Touch of Mink', 'Doris Day'),\n",
       " ('The 300 Spartans', 'Richard Egan'),\n",
       " ('The Counterfeit Traitor', 'William Holden'),\n",
       " ('The Exterminating Angel', 'Silvia Pinal'),\n",
       " ('The Four Days of Naples', 'Regina Bianchi'),\n",
       " ('The Intruder', 'William Shatner'),\n",
       " ('The L-Shaped Room', 'Leslie Caron'),\n",
       " ('The Loneliness of the Long Distance Runner', 'Tom Courtenay'),\n",
       " ('The Longest Day', 'John Wayne<br />'),\n",
       " ('The Man Who Shot Liberty Valance', 'James Stewart'),\n",
       " ('The Manchurian Candidate', 'Frank Sinatra'),\n",
       " ('The Music Man', 'Robert Preston'),\n",
       " ('The Phantom of the Opera', 'Herbert Lom'),\n",
       " ('The Premature Burial', 'Ray Milland'),\n",
       " ('The Trial', 'Anthony Perkins'),\n",
       " ('Through a Glass Darkly', 'Harriet Andersson'),\n",
       " ('To Kill a Mockingbird', 'Gregory Peck'),\n",
       " ('Tower of London', 'Vincent Price'),\n",
       " ('Two Half Times in Hell', 'Imre Sinkovits'),\n",
       " ('Varan the Unbelievable', 'KÃ´zÃ´ Nomura'),\n",
       " ('Village of Daughters', 'Eric Sykes'),\n",
       " ('What Ever Happened to Baby Jane?', 'Bette Davis'),\n",
       " (\"Who's Got the Action?\", 'Dean Martin')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, name\n",
    "  FROM (movie RIGHT JOIN casting ON movie.id=movieid) JOIN actor ON actorid=actor.id\n",
    "    WHERE yr=1962 AND ord=1;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Busy years for Rock Hudson\n",
    "\n",
    "Which were the busiest years for 'Rock Hudson', show the year and the number of movies he made each year for any year in which he made more than 2 movies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>yr</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1953</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1961</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1953, 5), (1961, 3)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT yr,COUNT(title) FROM\n",
    "  movie JOIN casting ON movie.id=movieid\n",
    "        JOIN actor   ON actorid=actor.id\n",
    "    WHERE name='Rock Hudson'\n",
    "    GROUP BY yr\n",
    "    HAVING COUNT(title) > 2;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Lead actor in Julie Andrews movies\n",
    "\n",
    "List the film title and the leading actor for all of the films 'Julie Andrews' played in.\n",
    "\n",
    "> _Did you get \"Little Miss Marker twice\"?_   \n",
    "> Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).\n",
    ">\n",
    "> Title is not a unique field, create a table of IDs in your subquery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "19 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>title</th>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Darling Lili</td>\n",
       "        <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Victor Victoria</td>\n",
       "        <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Little Miss Marker</td>\n",
       "        <td>Walter Matthau</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Despicable Me</td>\n",
       "        <td>Steve Carell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Star!</td>\n",
       "        <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Princess Diaries</td>\n",
       "        <td>Anne Hathaway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shrek the Third</td>\n",
       "        <td>Mike Myers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Relative Values</td>\n",
       "        <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Americanization of Emily</td>\n",
       "        <td>James Garner</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Torn Curtain</td>\n",
       "        <td>Paul Newman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Hawaii</td>\n",
       "        <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Thoroughly Modern Millie</td>\n",
       "        <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mary Poppins</td>\n",
       "        <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tooth Fairy</td>\n",
       "        <td>Dwayne Johnson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Tamarind Seed</td>\n",
       "        <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Pink Panther Strikes Again</td>\n",
       "        <td>Peter Sellers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>10</td>\n",
       "        <td>Dudley Moore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Princess Diaries 2: Royal Engagement</td>\n",
       "        <td>Anne Hathaway</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Duet for One</td>\n",
       "        <td>Julie Andrews</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Darling Lili', 'Julie Andrews'),\n",
       " ('Victor Victoria', 'Julie Andrews'),\n",
       " ('Little Miss Marker', 'Walter Matthau'),\n",
       " ('Despicable Me', 'Steve Carell'),\n",
       " ('Star!', 'Julie Andrews'),\n",
       " ('The Princess Diaries', 'Anne Hathaway'),\n",
       " ('Shrek the Third', 'Mike Myers'),\n",
       " ('Relative Values', 'Julie Andrews'),\n",
       " ('The Americanization of Emily', 'James Garner'),\n",
       " ('Torn Curtain', 'Paul Newman'),\n",
       " ('Hawaii', 'Julie Andrews'),\n",
       " ('Thoroughly Modern Millie', 'Julie Andrews'),\n",
       " ('Mary Poppins', 'Julie Andrews'),\n",
       " ('Tooth Fairy', 'Dwayne Johnson'),\n",
       " ('The Tamarind Seed', 'Julie Andrews'),\n",
       " ('The Pink Panther Strikes Again', 'Peter Sellers'),\n",
       " ('10', 'Dudley Moore'),\n",
       " ('The Princess Diaries 2: Royal Engagement', 'Anne Hathaway'),\n",
       " ('Duet for One', 'Julie Andrews')]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, name\n",
    "  FROM (movie RIGHT JOIN casting ON movie.id=movieid) JOIN actor ON actorid=actor.id\n",
    "    WHERE ord=1 AND movie.id IN \n",
    "    (SELECT movieid FROM casting JOIN actor ON actorid=actor.id WHERE name = 'Julie Andrews');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. Actors with 15 leading roles\n",
    "\n",
    "Obtain a list, in alphabetical order, of actors who've had at least 15 **starring** roles."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "100 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Adam Sandler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Al Pacino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Anthony Hopkins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Antonio Banderas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Arnold Schwarzenegger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Barbara Stanwyck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Basil Rathbone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ben Affleck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bette Davis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Betty Grable</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bing Crosby</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bruce Willis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bud Abbott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Burt Lancaster</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Burt Reynolds</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Buster Keaton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cary Grant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Charles Bronson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Charlton Heston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Clark Gable</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">100 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Adam Sandler',),\n",
       " ('Al Pacino',),\n",
       " ('Anthony Hopkins',),\n",
       " ('Antonio Banderas',),\n",
       " ('Arnold Schwarzenegger',),\n",
       " ('Barbara Stanwyck',),\n",
       " ('Basil Rathbone',),\n",
       " ('Ben Affleck',),\n",
       " ('Bette Davis',),\n",
       " ('Betty Grable',),\n",
       " ('Bing Crosby',),\n",
       " ('Bruce Willis',),\n",
       " ('Bud Abbott',),\n",
       " ('Burt Lancaster',),\n",
       " ('Burt Reynolds',),\n",
       " ('Buster Keaton',),\n",
       " ('Cary Grant',),\n",
       " ('Charles Bronson',),\n",
       " ('Charlton Heston',),\n",
       " ('Clark Gable',),\n",
       " ('Claudette Colbert',),\n",
       " ('Clint Eastwood',),\n",
       " ('Dean Martin',),\n",
       " ('Dennis Quaid',),\n",
       " ('Denzel Washington',),\n",
       " ('Dirk Bogarde',),\n",
       " ('Dustin Hoffman',),\n",
       " ('Edward G. Robinson',),\n",
       " ('Elvis Presley',),\n",
       " ('Errol Flynn',),\n",
       " ('Frank Sinatra',),\n",
       " ('Fred Astaire',),\n",
       " ('Fredric March',),\n",
       " ('Gary Cooper',),\n",
       " ('Gene Hackman',),\n",
       " ('George Clooney',),\n",
       " ('Glenn Ford',),\n",
       " ('Gregory Peck',),\n",
       " ('Greta Garbo',),\n",
       " ('Harrison Ford',),\n",
       " ('Henry Fonda',),\n",
       " ('Humphrey Bogart',),\n",
       " ('Ingrid Bergman',),\n",
       " ('Jack Lemmon',),\n",
       " ('Jack Nicholson',),\n",
       " ('Jackie Chan',),\n",
       " ('James Cagney',),\n",
       " ('James Mason',),\n",
       " ('James Stewart',),\n",
       " ('Jane Fonda',),\n",
       " ('Jean-Claude Van Damme',),\n",
       " ('Jeff Bridges',),\n",
       " ('Jerry Lewis',),\n",
       " ('Jim Carrey',),\n",
       " ('Joan Crawford',),\n",
       " ('John Barrymore',),\n",
       " ('John Travolta',),\n",
       " ('John Wayne',),\n",
       " ('Johnny Depp',),\n",
       " ('Keanu Reeves',),\n",
       " ('Kevin Costner',),\n",
       " ('Kirk Douglas',),\n",
       " ('Kurt Russell',),\n",
       " ('Lana Turner',),\n",
       " ('Laurence Olivier',),\n",
       " ('Leonardo DiCaprio',),\n",
       " ('Marlon Brando',),\n",
       " ('Mary Pickford',),\n",
       " ('Matt Damon',),\n",
       " ('Matt Dillon',),\n",
       " ('Mel Gibson',),\n",
       " ('Meryl Streep',),\n",
       " ('Michael Caine',),\n",
       " ('Michael Douglas',),\n",
       " ('Michael Keaton',),\n",
       " ('Nicolas Cage',),\n",
       " ('Paul Newman',),\n",
       " ('Peter Sellers',),\n",
       " ('Randolph Scott',),\n",
       " ('Robert De Niro',),\n",
       " ('Robert Mitchum',),\n",
       " ('Robert Taylor',),\n",
       " ('Robin Williams',),\n",
       " ('Rock Hudson',),\n",
       " ('Ronald Colman',),\n",
       " ('Sean Connery',),\n",
       " ('See below',),\n",
       " ('Shirley Temple',),\n",
       " ('Spencer Tracy',),\n",
       " ('Stan Laurel',),\n",
       " ('Steve Martin',),\n",
       " ('Sylvester Stallone',),\n",
       " ('Tom Berenger',),\n",
       " ('Tom Cruise',),\n",
       " ('Tom Hanks',),\n",
       " ('Tommy Lee Jones',),\n",
       " ('Tyrone Power',),\n",
       " ('Walter Matthau',),\n",
       " ('William Garwood',),\n",
       " ('William Holden',)]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name\n",
    "  FROM casting JOIN actor ON actorid=actor.id\n",
    "    WHERE ord=1\n",
    "    GROUP BY name\n",
    "    HAVING COUNT(actorid) >= 15\n",
    "    ORDER BY name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 14.\n",
    "List the films released in the year 1978 ordered by the number of actors in the cast, then by title."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "104 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>title</th>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Bad News Bears Go to Japan</td>\n",
       "        <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Swarm</td>\n",
       "        <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Grease</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>American Hot Wax</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Boys from Brazil</td>\n",
       "        <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Heaven Can Wait</td>\n",
       "        <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Big Wednesday</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A Night Full of Rain</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>A Wedding</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Orchestra Rehearsal</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Cheap Detective</td>\n",
       "        <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Go Tell the Spartans</td>\n",
       "        <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Death on the Nile</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Movie Movie</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Superman</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Cat from Outer Space</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The Driver</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Blue Collar</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ice Castles</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>International Velvet</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">104 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('The Bad News Bears Go to Japan', 50),\n",
       " ('The Swarm', 37),\n",
       " ('Grease', 28),\n",
       " ('American Hot Wax', 27),\n",
       " ('The Boys from Brazil', 26),\n",
       " ('Heaven Can Wait', 25),\n",
       " ('Big Wednesday', 21),\n",
       " ('A Night Full of Rain', 19),\n",
       " ('A Wedding', 19),\n",
       " ('Orchestra Rehearsal', 19),\n",
       " ('The Cheap Detective', 19),\n",
       " ('Go Tell the Spartans', 18),\n",
       " ('Death on the Nile', 17),\n",
       " ('Movie Movie', 17),\n",
       " ('Superman', 17),\n",
       " ('The Cat from Outer Space', 17),\n",
       " ('The Driver', 17),\n",
       " ('Blue Collar', 16),\n",
       " ('Ice Castles', 16),\n",
       " ('International Velvet', 16),\n",
       " (\"J.R.R. Tolkien's The Lord of the Rings\", 16),\n",
       " ('Alexandria... Why?', 15),\n",
       " ('Bye Bye Monkey', 15),\n",
       " ('Coming Home', 15),\n",
       " ('David', 15),\n",
       " ('Gray Lady Down', 15),\n",
       " ('Occupation in 26 Pictures', 15),\n",
       " ('Revenge of the Pink Panther', 15),\n",
       " (\"The Brink's Job\", 15),\n",
       " ('The Chant of Jimmie Blacksmith', 15),\n",
       " ('The Water Babies', 15),\n",
       " ('Violette NoziÃ¨re', 15),\n",
       " (\"Who'll Stop The Rain\", 15),\n",
       " ('Without Anesthesia', 15),\n",
       " ('Bread and Chocolate', 14),\n",
       " ('Closed Circuit', 14),\n",
       " ('Damien: Omen II', 14),\n",
       " ('I Wanna Hold Your Hand', 14),\n",
       " ('The Empire of Passion', 14),\n",
       " ('Almost Summer', 13),\n",
       " ('An Unmarried Woman', 13),\n",
       " ('Foul Play', 13),\n",
       " (\"Goin' South\", 13),\n",
       " ('The Left-Handed Woman', 13),\n",
       " ('California Suite', 12),\n",
       " ('Force 10 From Navarone', 12),\n",
       " ('In Praise of Older Women', 12),\n",
       " ('Jaws 2', 12),\n",
       " ('Midnight Express', 12),\n",
       " ('Piranha', 12),\n",
       " ('The End', 12),\n",
       " ('Autumn Sonata', 11),\n",
       " ('Comes a Horseman', 11),\n",
       " ('Days of Heaven', 11),\n",
       " ('Don', 11),\n",
       " (\"Sgt. Pepper's Lonely Hearts Club Band\", 11),\n",
       " ('Up in Smoke', 11),\n",
       " ('Eyes of Laura Mars', 10),\n",
       " ('Invasion of the Body Snatchers', 10),\n",
       " ('Muqaddar Ka Sikandar', 10),\n",
       " (\"National Lampoon's Animal House\", 10),\n",
       " ('Zero to Sixty', 10),\n",
       " ('Fingers', 9),\n",
       " ('FM', 9),\n",
       " (\"Shogun's Samurai\", 9),\n",
       " ('The Boys in Company C', 9),\n",
       " ('The Wiz', 9),\n",
       " ('Fedora', 8),\n",
       " ('The Ascent', 8),\n",
       " ('Attack of the Killer Tomatoes', 7),\n",
       " ('Hooper', 7),\n",
       " ('Kondura (Anugraham in Telugu)', 7),\n",
       " ('The Big Fix', 7),\n",
       " ('The Fury', 7),\n",
       " ('Girlfriends', 6),\n",
       " ('Watership Down', 6),\n",
       " ('House Calls', 5),\n",
       " ('La Cage aux Folles', 5),\n",
       " ('Rabbit Test', 5),\n",
       " ('Rockers', 5),\n",
       " (\"Snake in the Eagle's Shadow\", 5),\n",
       " ('The Buddy Holly Story', 5),\n",
       " ('The Deer Hunter', 5),\n",
       " ('The Herd', 5),\n",
       " ('The Last Waltz', 5),\n",
       " ('You Are Not Alone', 5),\n",
       " ('Drunken Master', 4),\n",
       " ('Get Out Your Handkerchiefs', 4),\n",
       " ('Halloween', 4),\n",
       " ('Hot Lead and Cold Feet', 4),\n",
       " ('Passion Flower Hotel', 4),\n",
       " ('American Boy: A Profile of Steven Prince', 3),\n",
       " ('Coup de GrÃ¢ce', 3),\n",
       " ('Lady on the Bus', 3),\n",
       " ('Pretty Baby', 3),\n",
       " ('Renaldo and Clara', 3),\n",
       " ('The King of the Street Cleaners', 3),\n",
       " ('The One and Only', 3),\n",
       " ('Enter the Fat Dragon', 2),\n",
       " ('Lies My Father Told Me', 2),\n",
       " ('Same Time, Next Year', 2),\n",
       " ('Somebody Killed Her Husband', 2),\n",
       " (\"That's Carry On!\", 2),\n",
       " ('The 36th Chamber of Shaolin', 2)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT title, COUNT(actorid)\n",
    "  FROM movie RIGHT JOIN casting ON movie.id=movieid\n",
    "    WHERE yr = 1978\n",
    "    GROUP BY title\n",
    "    ORDER BY COUNT(actorid) DESC, title;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 15.\n",
    "\n",
    "List all the people who have worked with 'Art Garfunkel'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "36 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peter Bogdanovich</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Frederique van der Wal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Heidi Klum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Salma Hayek</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sela Ward</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ryan Phillippe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ron Jeremy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ellen Albertini Dow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harris Yulin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mark Ruffalo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Stars on 54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Michael York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Neve Campbell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bruce Jay Friedman</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Donald Trump</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Heather Matarazzo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Veronica Webb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mike Myers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sherry Stringfield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Valerie Perrine</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">36 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Peter Bogdanovich',),\n",
       " ('Frederique van der Wal',),\n",
       " ('Heidi Klum',),\n",
       " ('Salma Hayek',),\n",
       " ('Sela Ward',),\n",
       " ('Ryan Phillippe',),\n",
       " ('Ron Jeremy',),\n",
       " ('Ellen Albertini Dow',),\n",
       " ('Harris Yulin',),\n",
       " ('Mark Ruffalo',),\n",
       " ('Stars on 54',),\n",
       " ('Michael York',),\n",
       " ('Neve Campbell',),\n",
       " ('Bruce Jay Friedman',),\n",
       " ('Donald Trump',),\n",
       " ('Heather Matarazzo',),\n",
       " ('Veronica Webb',),\n",
       " ('Mike Myers',),\n",
       " ('Sherry Stringfield',),\n",
       " ('Valerie Perrine',),\n",
       " ('Elio Fiorucci',),\n",
       " ('Bill Paxton',),\n",
       " ('Cindy Crawford',),\n",
       " ('Georgina Grenville',),\n",
       " ('Lauren Hutton',),\n",
       " ('Robert DoQui',),\n",
       " ('Julian Sands',),\n",
       " ('Thelma Houston',),\n",
       " ('Kurtwood Smith',),\n",
       " ('Sheryl Crow',),\n",
       " ('Lorna Luft',),\n",
       " ('Cecilie Thomsen',),\n",
       " ('Sherilyn Fenn',),\n",
       " ('Skipp Sudduth',),\n",
       " ('Breckin Meyer',),\n",
       " ('Beverly Johnson',)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT name\n",
    "  FROM (movie RIGHT JOIN casting ON movie.id=movieid) JOIN actor ON actorid=actor.id\n",
    "    WHERE movieid IN\n",
    "    (SELECT movieid FROM casting JOIN actor ON actorid=actor.id WHERE name = 'Art Garfunkel')\n",
    "    AND name <> 'Art Garfunkel';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
